package io.renren.modules.project.service.impl;

import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import io.renren.common.utils.poi.ExcelStyleHelper;
import io.renren.modules.project.controller.ChartQualityController;
import io.renren.modules.project.dao.ChartQualityDao;
import io.renren.modules.project.service.ChartQualityService;
import io.renren.modules.project.vo.ChartQualityVoEntity;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service("ChartQualityService")
public class ChartQualityServiceImpl extends ServiceImpl<ChartQualityDao, ChartQualityVoEntity> implements ChartQualityService {

    @Override
    public List<ChartQualityVoEntity> queryList(Map<String, Object> params){
        List<ChartQualityVoEntity> list = baseMapper.getChartQualityVoList( params );
        return list;
    }

    public HSSFSheet initSheet(HSSFWorkbook workbook,String sheetName,int rIndex) {
        //生成一个表格，设置表格名称
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //设置表格列宽度
        sheet.setColumnWidth(0, 170 * 100);
        sheet.setColumnWidth(1, 40 * 100);
        sheet.setColumnWidth(2, 40 * 100);
        sheet.setColumnWidth(3, 40 * 100);
        sheet.setColumnWidth(4, 40 * 100);
        sheet.setColumnWidth(5, 40 * 100);

        // 标题字体设置
        HSSFFont hfont = workbook.createFont();
        hfont.setFontName("黑体");
        hfont.setFontHeightInPoints((short) 16);
        hfont.setBold(true);   //字体加粗
        // 标题表格样式
        HSSFCellStyle hstyle = workbook.createCellStyle();
        hstyle.setFont(hfont);
        hstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  // 垂直居中
        hstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);       // 水平居中

        // 表头
        String[] titleWork = new String[]{"项目名称","质量评分","质量等级","项目负责人","质检员","质检审核员"};

        // 字段字体设置
        HSSFFont tfont = workbook.createFont();
        tfont.setFontName("宋体");
        tfont.setFontHeightInPoints((short) 12);
        tfont.setBold(true);   //字体加粗
        // 字段表格样式
        HSSFCellStyle tstyle = workbook.createCellStyle();
        tstyle.setFont(tfont);
        tstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  // 垂直居中

        // 字段行
        HSSFRow tRow = sheet.createRow(rIndex);
        tRow.setHeight((short) 420);
        for (int i=0; i< titleWork.length; i++) {
            HSSFCell tcell;
            tcell = tRow.createCell(i);
            tcell.setCellValue(new HSSFRichTextString(titleWork[i]));
            tcell.setCellStyle(tstyle);
        }
        return sheet;
    }

    @Override
    public HSSFWorkbook exportExcel(Map<String, Object> params){
        DecimalFormat decimalFormat=new DecimalFormat("0.00");
        List<ChartQualityVoEntity> list = this.queryList(params);

        String title = "";
        if (StringUtils.isNotBlank(params.get("startDate").toString())) {
            title += params.get("startDate").toString();
        }
        if (StringUtils.isNotBlank(params.get("endDate").toString())) {
            title += params.get("endDate").toString();
        } else {
            title += "至今";
        }
        title += "  质量统计表";

        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        int rowIndex = 0;

        //生成一个表格，设置表格名称
        HSSFSheet sheet = this.initSheet(workbook,"总表", 1);

        rowIndex++;
        // 标题字体设置
        HSSFFont hfont = workbook.createFont();
        hfont.setFontName("黑体");
        hfont.setFontHeightInPoints((short) 16);
        hfont.setBold(true);   //字体加粗
        // 标题表格样式
        HSSFCellStyle hstyle = workbook.createCellStyle();
        hstyle.setFont(hfont);
        hstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  // 垂直居中
        hstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);       // 水平居中

        // 标题行
        HSSFRow hRow = sheet.createRow(0);
        hRow.setHeight((short) 550);
        HSSFCell hcell = hRow.createCell(0);
        hcell.setCellValue(new HSSFRichTextString(title));
        hcell.setCellStyle(hstyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));

        rowIndex = 2;

        // 字段字体设置
        HSSFFont tfont = workbook.createFont();
        tfont.setFontName("宋体");
        tfont.setFontHeightInPoints((short) 12);
        tfont.setBold(true);   //字体加粗
        // 字段表格样式
        HSSFCellStyle tstyle = workbook.createCellStyle();
        tstyle.setFont(tfont);
        tstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  // 垂直居中

        String groupName = "";
        List<GroupScoreItem> scoreList = new ArrayList<>();
        for( int i=0; i<list.size(); i++){
            if(list.get(i).getgroupName() != null) {
                GroupScoreItem item = new GroupScoreItem();
                item.groupName = list.get(i).getgroupName();
                groupName = list.get(i).getgroupName();
                // 查找该工作组的所有质量评分列表
                for (int t = i; t < list.size(); t++) {
                    if (groupName.equals(list.get(t).getgroupName())) {
                        item.qualityList.add(list.get(t));
                        item.setLevelNum(list.get(t).getqualityScore() == null ? 0 : list.get(t).getqualityScore());
                        i=t;
                    } else {
                        i = t - 1;
                        break;
                    }
                }
                scoreList.add(item);
            }
        }

        for(GroupScoreItem item : scoreList){
            // 总表
            // 各队明细产值
            // 工作组名字体设置
            HSSFFont gfont = workbook.createFont();
            gfont.setFontName("宋体");
            gfont.setFontHeightInPoints((short) 11);
            gfont.setBold(true);   //字体加粗
            HSSFCellStyle gstyle = workbook.createCellStyle();
            gstyle.setFont(gfont);
            gstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  // 垂直居中
            sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 5));
            HSSFRow gRow = sheet.createRow(rowIndex++);
            gRow.setHeight((short) 300);
            HSSFCell gcell = gRow.createCell(0);
            gcell.setCellValue(new HSSFRichTextString(item.groupName));
            gcell.setCellStyle(gstyle);

            // 各组各项目评分明细
            for( ChartQualityVoEntity entity : item.qualityList){
                HSSFRow cRow = sheet.createRow(rowIndex++);
                cRow.setHeight((short) 280);
                HSSFCell cell;
                cell = cRow.createCell(0);cell.setCellValue(new HSSFRichTextString( entity.getprojectName() ));
                cell = cRow.createCell(1);cell.setCellValue(new HSSFRichTextString( entity.getqualityScore()!= null? entity.getqualityScore().toString(): "" ));
                cell = cRow.createCell(2);cell.setCellValue(new HSSFRichTextString( entity.getqualityScore()!= null? getLevel(entity.getqualityScore()): ""));
                cell = cRow.createCell(3);cell.setCellValue(new HSSFRichTextString( entity.getprojectCharge()));
                cell = cRow.createCell(4);cell.setCellValue(new HSSFRichTextString( entity.getqualityUserName() ));
                cell = cRow.createCell(5);cell.setCellValue(new HSSFRichTextString( entity.getqualityConfirmName() ));
            }

            sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 5));
            gRow = sheet.createRow(rowIndex++);
            gRow.setHeight((short) 300);
            gcell = gRow.createCell(0);
            gcell.setCellValue(new HSSFRichTextString( item.groupName +  ":合计 "  + item.projectNum  + " 个项目" ));
            gcell.setCellStyle(gstyle);
            gcell = gRow.createCell(1);
            String totalAmount = "优:" + String.valueOf(item.exceNum) + "项；" +
                    "良:" + String.valueOf(item.fineNum) + "项；" +
                    "及格:" + String.valueOf(item.passNum) + "项；" +
                    "不及格:" + String.valueOf(item.badNum) + "项；" +
                    "优良:" + String.valueOf(item.exceNum + item.fineNum) + "项；" +
                    "优良率:" + decimalFormat.format((float)(item.exceNum + item.fineNum) * 100 / item.projectNum ) + "%；";
            gcell.setCellValue(new HSSFRichTextString( totalAmount));
            gcell.setCellStyle(gstyle);

            // 分表
            //生成一个表格，设置表格名称
            int cIndex = 0;
            HSSFSheet childSheet = this.initSheet(workbook,item.groupName,cIndex);
            cIndex++;
            HSSFRow gcRow = childSheet.createRow(cIndex++);
            gcRow.setHeight((short) 300);
            gcell = gcRow.createCell(0);
            gcell.setCellValue(new HSSFRichTextString(item.groupName));
            gcell.setCellStyle(gstyle);

            // 各组各项目评分明细
            for( ChartQualityVoEntity entity : item.qualityList){
                HSSFRow ccRow = childSheet.createRow(cIndex++);
                ccRow.setHeight((short) 280);
                HSSFCell cell;
                cell = ccRow.createCell(0);cell.setCellValue(new HSSFRichTextString( entity.getprojectName() ));
                cell = ccRow.createCell(1);cell.setCellValue(new HSSFRichTextString( entity.getqualityScore()!= null? entity.getqualityScore().toString(): "" ));
                cell = ccRow.createCell(2);cell.setCellValue(new HSSFRichTextString( entity.getqualityScore()!= null? getLevel(entity.getqualityScore()): ""));
                cell = ccRow.createCell(3);cell.setCellValue(new HSSFRichTextString( entity.getprojectCharge()));
                cell = ccRow.createCell(4);cell.setCellValue(new HSSFRichTextString( entity.getqualityUserName() ));
                cell = ccRow.createCell(5);cell.setCellValue(new HSSFRichTextString( entity.getqualityConfirmName() ));

            }

            childSheet.addMergedRegion(new CellRangeAddress(cIndex, cIndex, 1, 5));
            gRow = childSheet.createRow(cIndex++);
            gRow.setHeight((short) 300);
            gcell = gRow.createCell(0);
            gcell.setCellValue(new HSSFRichTextString( item.groupName +  ":合计 "  + item.projectNum  + " 个项目" ));
            gcell.setCellStyle(gstyle);
            gcell = gRow.createCell(1);
            totalAmount = "优:" + String.valueOf(item.exceNum) + "项；" +
                    "良:" + String.valueOf(item.fineNum) + "项；" +
                    "及格:" + String.valueOf(item.passNum) + "项；" +
                    "不及格:" + String.valueOf(item.badNum) + "项；" +
                    "优良:" + String.valueOf(item.exceNum + item.fineNum) + "项；" +
                    "优良率:" + decimalFormat.format((float)(item.exceNum + item.fineNum) * 100 / item.projectNum ) + "%；";
            gcell.setCellValue(new HSSFRichTextString( totalAmount));
            gcell.setCellStyle(gstyle);

        }
        // 总表合计
        sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 5));
        HSSFRow row = sheet.createRow(rowIndex++);
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFCell rcell = row.createCell(0);
        rcell.setCellStyle(style);
        ExcelStyleHelper.setCellRightTop(rcell);
        ExcelStyleHelper.setFontColor(workbook,rcell, Font.COLOR_RED);

        int exceAllNum = 0;
        int fineAllNum = 0;
        int passAllNum = 0;
        int badAllNum = 0;
        int projectAllNum = 0;
        for(GroupScoreItem item : scoreList){
            exceAllNum += item.exceNum;
            fineAllNum += item.fineNum;
            passAllNum += item.passNum;
            badAllNum += item.badNum;
            projectAllNum += item.projectNum;
        }
        String totalStr = "优:" + String.valueOf(exceAllNum) + "项；" +
                "良:" + String.valueOf(fineAllNum) + "项；" +
                "及格:" + String.valueOf(passAllNum) + "项；" +
                "不及格:" + String.valueOf(badAllNum) + "项；" +
                "优良:" + String.valueOf(exceAllNum + fineAllNum) + "项；" +
                "优良率:" + decimalFormat.format((float)(exceAllNum + fineAllNum) * 100 / projectAllNum ) + "%；";
        rcell.setCellValue(new HSSFRichTextString( "总共合计项目:" + projectAllNum + "个   " + totalStr ));

        return workbook;
    }


    //质量等级返回
    public String getLevel(float qualityScore){
        if (qualityScore < 60) {
            return  "不合格";
        } else if (qualityScore >= 60 && qualityScore <= 70) {
            return "合格";
        } else if (qualityScore > 70 && qualityScore < 90) {
            return  "良";
        } else if (qualityScore >= 90) {
            return  "优";
        }
        return "";
    }

    // 工作组质量统计类
    public class GroupScoreItem{
        public String groupName;
        public int exceNum;  // 优秀数量
        public int fineNum;  // 良好数量
        public int passNum;  // 及格数量
        public int badNum;  // 不及格数量
        public int projectNum;  // 项目数
        public List<ChartQualityVoEntity> qualityList; // 工作组项目质量列表

        public GroupScoreItem(){
            groupName = "";
            exceNum = 0;
            fineNum = 0;
            passNum = 0;
            badNum = 0;
            projectNum = 0;
            qualityList = new ArrayList<>();
        }

        // 通过分数设置 优良中差数量
        public void setLevelNum( float qualityScore){
            if (qualityScore < 60) {
                this.badNum ++;
            } else if (qualityScore >= 60 && qualityScore <= 70) {
                this.passNum ++;
            } else if (qualityScore > 70 && qualityScore < 90) {
                this.fineNum ++;
            } else if (qualityScore >= 90) {
                this.exceNum ++;
            }
            projectNum ++;
        }
    }
}
